Release 10.1A: OpenEdge Development:
Progress 4GL Handbook

Record locking examples

A few simple examples can help illustrate how Progress handles different kinds of locks. When you start the AppBuilder and connect to the Sports2000 database, you are running in single-user mode, as the only user of the database.

To set up your session so you can test locking:
  1. Exit your session to free up your single-user connection to the Sports2000 database.

  2. Make sure your path includes the bin directory under your OpenEdge install directory.

  3. From a Windows Command Prompt window, change your directory to your working directory, or wherever your local Sports2000 database is located.

  4. Type the command: proserve Sports2000

    5. You should see a series of messages as the server starts up.

  5. Restart the AppBuilder.

  6. From the Tools menu, select Database Connections.

  7. Click the Connect button.

  8. Type Sports2000 as the Physical Name, then click the Options button:

  9. Check on the Multiple Users toggle box so that you connect to the server in multi-user mode:

  10. Click OK, then close the Database Connections dialog box:

Now your session is connected to the database server. To test the effects of record locking in a multi-user environment, you need to create a second OpenEdge session.

To start up another session:
  1. Start the OpenEdge Desktop.

  2. From the Tools menu, select Data Administration:

  3. From the Data Administration menu, select Database, then click Connect.

  4. Go through the same sequence of steps as before to connect in multi-user mode to the same Sports2000 database server.

Now you’re ready to test locking conflicts.

Using EXCLUSIVE-LOCKs

Your first test involves using EXCLUSIVE-LOCKs.

To test locking conflicts when using an EXCUSIVE-LOCK:
  1. Bring up a New Procedure Window from the AppBuilder in your first session.

  2. Enter this code to retrieve the first Customer record with an exclusive lock:

  3. From the Desktop in your second session, bring up the Procedure Editor.

  4. Enter the same procedure, but with a message that says User 2 instead of User 1:

  5. Run the first procedure. The window for the first session comes up:

  6. Run the procedure in the second session. Because it’s trying to get an exclusive lock on a record already locked by the other process, you get a message telling you that the record is in use and that you must either wait or cancel out of the FIND statement:

    7. If you close the first window, the second process can now read and lock the Customer record:

This example illustrates the most basic rule of record locking. Only one user can have an EXCLUSIVE-LOCK on a record at a time. Any other user trying to lock the same record must either wait for it or cancel the request.

Using and upgrading SHARE-LOCKS

If you remove the EXCLUSIVE-LOCK keyword from the FIND statements in both procedures, Progress reads the record in each case with a SHARE-LOCK by default. The version of the procedure shown in Figure 15–2 is saved in the examples as h-findCustUser1.p. For this test, the second user runs the same procedure with the displayed string “User 2”.

Figure 15–2: h-findCustUser1.p procedure

You can run both procedures at the same time, and they can both access the Customer with a SHARE-LOCK, as shown in Figure 15–3.

Figure 15–3: Result of two sessions running h-findCustUser1.p procedure

However, if you enter a new value in the CreditLimit field for either one and tab out of the field, Progress tries to upgrade the lock to an EXCLUSIVE-LOCK to update the record. This attempt fails with the message shown in Figure 15–4 because the other user has the record under SHARE-LOCK.

Figure 15–4: SHARE-LOCK status message

If both users try to update the record, they both get the lock conflict message. This situation is called a deadly embrace, because neither user can proceed until one of them cancels out of the update, releasing the SHARE-LOCK so that the other can upgrade the lock and update the record.

To avoid this kind of conflict, it is better to read a record you intend to update with an EXCLUSIVE-LOCK in the first place. If you do this in a server-side business logic procedure, which in a modern application is always the case, you won’t see a message if the record is locked by another session. Your session simply waits until the lock is freed. If your record locks are confined to server-side procedures with no user interface or other blocking statements, then the problem of a record being locked indefinitely won’t ever happen, and a brief wait for a record is not normally a problem.

By default, the time that a session waits for a record to be unlocked is 30 minutes. However, no message is sent back to the process when the time out value is reached. You can specify a different wait time with the Lock Timeout (-lkwtmo) startup parameter.

Using the NO-WAIT Option with the AVAILABLE and LOCKED functions

Just for the record, there are options you can use to make other choices in your procedures in the case of lock contention. If for some reason you do not want your procedure to wait for the release of a lock, you can include the NO-WAIT keyword on the FIND statement or FOR EACH loop. Normally, you should also include the NO-ERROR keyword on the statement to avoid the default “record is locked” message from Progress. Following the statement, you can use one of two built-in functions to test whether your procedure got the record it wanted. The following variant of the procedure for User 2 uses the AVAILABLE keyword to test for the record, and is saved as h-findCustUser2.p. Because the NO-WAIT option causes the FIND statement in this procedure to fail and execution to continue if the record is already locked by another session, the record is not in the buffer, and the AVAILABLE(Customer) function returns false: 

/* h-findCustUser2.p */ 
FIND Customer 1 EXCLUSIVE-LOCK NO-WAIT NO-ERROR. 
IF NOT AVAILABLE(Customer) THEN 
DO: 
    MESSAGE "That Customer isn't available for update." 
        VIEW-AS ALERT-BOX. 
END. 
ELSE DO: 
    DISPLAY "User 2:" CustNum FORMAT "ZZZ9" NAME FORMAT "X(12)" 
                               CreditLimit Balance 
                                 WITH FRAME CustFrame. 
    ON 'LEAVE':U OF Customer.CreditLimit IN FRAME CustFrame 
    DO: 
        ASSIGN Customer.CreditLimit. 
    END. 
         
    ENABLE Customer.CreditLimit Balance WITH FRAME CustFrame. 
    WAIT-FOR CLOSE OF THIS-PROCEDURE. 
END.

If you run h-findCustUser1.p and h-findCustUser2.p, in that order from different sessions, the second session displays the MESSAGE statement, as shown in Figure 15–5.

Figure 15–5: EXCLUSIVE-LOCK message

This message occurs because h-findCustUser1.p has read the record with a SHARE-LOCK and h-findCustUser2.p has attempted to read it with an EXCLUSIVE-LOCK, which fails.

Alternatively, if you want to distinguish between the case where the record is not available because it has been locked by another user and the case where the record wasn’t found because the selection was invalid in some way, you can use the LOCKED function: 

FIND Customer 1 EXCLUSIVE-LOCK NO-WAIT NO-ERROR. 
IF LOCKED(Customer) THEN 
    MESSAGE "That Customer isn't available for update." 
        VIEW-AS ALERT-BOX. 
ELSE IF NOT AVAILABLE(Customer) THEN 
    MESSAGE "That Customer was not found." 
        VIEW-AS ALERT-BOX. 
          . 
          . 
          .

Once again, because SHARE-LOCKS are of very limited use in application procedures that are distributed or might be distributed between sessions, it is good practice to bypass this method of reading records altogether and always read records with an EXCLUSIVE-LOCK if you know that your procedure updates them immediately.

Also, because the NO-WAIT option is designed for possible record contention in an environment where a user might hold a record lock for a long time while viewing the record on screen or going out for lunch, this option is not normally needed in an application where all database access is confined to the server, where there should be no user interaction with locked records. If you code your application properly to make sure that record locks are not held unnecessarily, then lock contention should almost never be an issue.

Reading records with NO-LOCK

If you want to read records regardless of whether they are locked by other users, you can use a third NO-LOCK option. This option lets you read a record without ever being prevented from doing so by another user’s lock. If you do not intend to update the record and are simply reading the data, this is an appropriate option. You must always be aware that reading records with NO-LOCK means that you might read parts of an incomplete transaction that has written some but not all of its changes to the database. In some cases, you can even read a record that has been newly written to the database with its key fields and index information, but not with the changes to other fields in the record. NO-LOCK is the default locking level for queries that have an associated browse, since you would normally not want to lock a whole set of records simply to browse them.

You cannot upgrade a record’s lock level from NO-LOCK to EXCLUSIVE-LOCK. If you try to update a record you’ve read with NO-LOCK, you get an error message from Progress, such as the one shown in Figure 15–6.

Figure 15–6: NO-LOCK error message

You must FIND the record again with an EXCLUSIVE-LOCK if you need to update it.

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095